package com.F03.action;


import java.util.ArrayList;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.log4j.Logger;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import com.F03.actionform.F0306ActionForm;
import com.F03.dao.F0306DAO;
import com.action.BaseAction;
import com.config.Constants;
import com.info.T_S_YHCZXXinfo;
import com.util.ExcelExport;


/**
 * 
 * @file F0306Action.java
 * @author huqy
 * @since 2009-3-15
 * @version 1.0
 */
public class F0306Action extends BaseAction {

	/**
	 * Method execute
	 * @param mapping
	 * @param form
	 * @param request
	 * @param response
	 * @return ActionForward
	 */
	Logger log = Logger.getLogger(F0306Action.class);

	/** 操作记录INFO */
	T_S_YHCZXXinfo t_YhczxxInfo = null;
	
	public ActionForward execute(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response){
		F0306ActionForm actionForm = (F0306ActionForm) form;
		F0306DAO dao = new F0306DAO();
		String operationType = actionForm.getOperationType();
		String strList = "list";
		
		try {
			//操作记录
			t_YhczxxInfo = setYhczxx(request, response, operationType,"F0306");

			/*
			if(Constants.BACK.equals(operationType)){
				return mapping.findForward(strList);
			}
			*/

			if(Constants.QUERYLIST.equals(operationType)){
				actionForm.setMaterialCostTotal("");
				actionForm.setUnitPriceTotal("");
				actionForm.setTotalPriceTotal("");
				actionForm.setConsumptionTotal("");
				actionForm.setConsumptionRateTotal("");
				doQueryList(actionForm,dao.queryProductConsumptionList(actionForm));
				//actionForm.setList(dao.queryProductConsumptionList(actionForm));
				//actionForm.setTotalList(dao.queryTotalList(info));
				return mapping.findForward(strList);
			}
			
			else if(Constants.INIT.equals(operationType)){
				return mapping.findForward(strList);
			}
			else if("export".equals(operationType)){
				doExport(actionForm, dao, response);
				return mapping.findForward("list");
			}
		} catch (Exception e){
			e.printStackTrace();
		} finally {
			if (dao != null) dao.disConnect();
		}
		return mapping.findForward(strList);
	}
	
	private void doQueryList(F0306ActionForm actionForm,List list){
		if(list!=null&&list.size()>0){
			actionForm.setList(list);
		}
		else{
			actionForm.setList(list);
			actionForm.setErrorFlag("2");
		}	
	}
	
	private void doExport(F0306ActionForm actionForm, F0306DAO dao, HttpServletResponse response){
		List titleList = new ArrayList();
		List totalList = new ArrayList();
		try {
			String fileName = "生产消耗统计";
			/* 标题设定 */
			titleList.add("工作号");
			titleList.add("产品模型");
			titleList.add("材料成本");
			titleList.add("产品单价");
			titleList.add("产品总价");
			titleList.add("产品消耗(rmb)");
			titleList.add("消耗率%");
			
			/* sql作成 */
			String sql = getSql(actionForm);
			totalList = getF0306ListTotal(actionForm);
			/* excel输出 */
			ExcelExport.exportTotalOther(fileName, titleList,totalList, sql,actionForm, response);

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (dao != null)
				dao.disConnect();
		}
	}

	public List getF0306ListTotal(ActionForm actionForm){
		List collist = new ArrayList();
		F0306ActionForm form =(F0306ActionForm)actionForm;
		if(!form.getConsumptionTotal().equals("")){
			collist.add("总计");
			collist.add("");
			collist.add(form.getMaterialCostTotalXls());
			collist.add(form.getUnitPriceTotalXls());
			collist.add(form.getTotalPriceTotalXls());
			collist.add(form.getConsumptionTotalXls());
			collist.add(form.getConsumptionRateTotal());
		}
		return collist;
	}
	
	public String getSql(F0306ActionForm actionForm){
		StringBuffer sql=new StringBuffer();
		sql.append(" select g.manufacturing_order,k.name,g.material_cost,h.unit_price, ");
    	sql.append(" h.total_price,f.consumption, ");
    	sql.append(" CONVERT(NUMERIC(15,2),(CONVERT(float,g.material_cost)/CONVERT(float,h.unit_price))*100) as consumption_rate");
    	sql.append(" from t_not_standard_products g,c_orders h,t_product_model k,");
    	sql.append(" (select sum(e.consumption) as consumption,e.manufacturing_order from "); 
    	sql.append(" (select max(c.serial) as max_serial,a.manufacturing_order,a.groups ");
    	sql.append(" ,a.group_qty*adyh.FUN_GETWIPPRODUCTCOST(a.manufacturing_order,max(c.serial)) as consumption ");
    	sql.append(" from pd_production a,t_not_standard_products b,t_family c ");
    	sql.append(" where a.manufacturing_order = b.manufacturing_order and ");
    	sql.append(" b.family_code = c.family_code and a.last_advance = c.phases_code  "); 
    	if(!actionForm.getLastAdvanceDate1().equals("")){
    		sql.append(" and a.last_advance_date >=CONVERT(datetime,'"+actionForm.getLastAdvanceDate1()+"') ");
    	}
    	if(!actionForm.getLastAdvanceDate2().equals("")){
    		sql.append(" and a.last_advance_date <=CONVERT(datetime,'"+actionForm.getLastAdvanceDate2()+"') ");
    	}
    	sql.append(" group by a.manufacturing_order ,a.groups,a.group_qty ");
    	sql.append(" ) e group by e.manufacturing_order ) f ");
    	sql.append(" where g.manufacturing_order= f.manufacturing_order and g.model_type=k.code and ");
    	sql.append(" g.manufacturing_order = h.manufacturing_order ");
    	if(!actionForm.getManufacturingOrder1().equals("")){
    		sql.append(" and g.manufacturing_order>='"+actionForm.getManufacturingOrder1()+"' ");
    	}
    	if(!actionForm.getManufacturingOrder2().equals("")){
    		sql.append(" and g.manufacturing_order<='"+actionForm.getManufacturingOrder2()+"' ");
    	}
		return sql.toString();
	}

}
